import sqlite3
import akshare as ak
from datetime import datetime
import util
import pandas as pd
import json

conn, cursor = util.getDb()


def exec(symbol, start, end):
    # 说明 行业历史资金流
    # 接口: stock_sector_fund_flow_hist
    # 目标地址: https://data.eastmoney.com/bkzj/BK1034.html
    # 描述: 东方财富网-数据中心-资金流向-行业资金流-行业历史资金流
    # 限量: 单次获取指定行业的行业历史资金流数据
    cursor.execute("SELECT industry FROM stock_info WHERE symbol = ?", (symbol,))
    industry_row = cursor.fetchone()
    if not industry_row:
        print(f"No industry found for symbol {symbol}")
        return
    start = datetime.strptime(start, "%Y%m%d").strftime("%Y-%m-%d")
    end = datetime.strptime(end, "%Y%m%d").strftime("%Y-%m-%d")
    industry = industry_row[0]

    # Step 2: Check if data for this industry and date range already exists
    cursor.execute(
        """
        SELECT * FROM industry_daily
        WHERE industry = ? AND date BETWEEN ? AND ?
        """,
        (industry, start, end),
    )
    rows = cursor.fetchall()
    existing_dates = {row[0] for row in rows}
    # Step 3: Fetch and insert missing data
    if not (end in existing_dates):
        # Get historical stock_info data from Akshare
        stock_sector_fund_flow_hist_df = ak.stock_sector_fund_flow_hist(symbol=industry)

        # Ensure the date column is formatted correctly
        stock_sector_fund_flow_hist_df["日期"] = pd.to_datetime(
            stock_sector_fund_flow_hist_df["日期"]
        ).dt.date

        # Insert missing data into `industry_daily`
        for _, row in stock_sector_fund_flow_hist_df.iterrows():
            cursor.execute(
                """
                    INSERT INTO industry_daily (
                        industry, date, main_net_inflow, main_net_ratio,
                        huge_net_inflow, huge_net_ratio, large_net_inflow,
                        large_net_ratio, medium_net_inflow, medium_net_ratio,
                        small_net_inflow, small_net_ratio
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                (
                    industry,
                    row["日期"],
                    row["主力净流入-净额"],
                    row["主力净流入-净占比"],
                    row["超大单净流入-净额"],
                    row["超大单净流入-净占比"],
                    row["大单净流入-净额"],
                    row["大单净流入-净占比"],
                    row["中单净流入-净额"],
                    row["中单净流入-净占比"],
                    row["小单净流入-净额"],
                    row["小单净流入-净占比"],
                ),
            )
        conn.commit()
        # 获取所有历史交易日数据
        trade_dates = ak.tool_trade_date_hist_sina()["trade_date"].values
        today = datetime.today().date()

        # 检查今天是否是交易日
        if today in trade_dates:
            latest_trade_date = today
        else:
            # 如果今天不是交易日，找到今天以前最近的一个交易日
            latest_trade_date = max(date for date in trade_dates if date < today)

        print("最近一个交易日:", latest_trade_date)

        if (
            not latest_trade_date.strftime("%Y-%m-%d")
            in stock_sector_fund_flow_hist_df["日期"].astype(str).values
        ):
            # 今日
            stock_sector_fund_flow_rank_df1 = ak.stock_sector_fund_flow_rank(
                indicator="今日", sector_type="行业资金流"
            )
            for _, row in stock_sector_fund_flow_rank_df1.iterrows():
                cursor.execute(
                    """
                    INSERT INTO industry_daily (
                        industry, date, main_net_inflow, main_net_ratio,
                        huge_net_inflow, huge_net_ratio, large_net_inflow,
                        large_net_ratio, medium_net_inflow, medium_net_ratio,
                        small_net_inflow, small_net_ratio
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (
                        industry,
                        latest_trade_date,
                        row["主力净流入-净额"],
                        row["主力净流入-净占比"],
                        row["超大单净流入-净额"],
                        row["超大单净流入-净占比"],
                        row["大单净流入-净额"],
                        row["大单净流入-净占比"],
                        row["中单净流入-净额"],
                        row["中单净流入-净占比"],
                        row["小单净流入-净额"],
                        row["小单净流入-净占比"],
                    ),
                )
            conn.commit()
        # stock_sector_fund_flow_rank_df2 = ak.stock_sector_fund_flow_rank(
        #     indicator="今日", sector_type="板块资金流"
        # )
        # stock_sector_fund_flow_rank_df3 = ak.stock_sector_fund_flow_rank(
        #     indicator="今日", sector_type="地域资金流"
        # )
        cursor.execute(
            """
        SELECT * FROM industry_daily
        WHERE industry = ? AND date BETWEEN ? AND ?
        """,
            (industry, start, end),
        )
        rows = cursor.fetchall()
        # Get column names from the cursor
    column_names = [description[0] for description in cursor.description]

    # Convert rows to a list of dictionaries
    data = [dict(zip(column_names, row)) for row in rows]

    # Convert the list of dictionaries to JSON
    # json_data = json.dumps(data, ensure_ascii=False)
    conn.close()
    return data


if __name__ == "__main__":
    print(exec("002410", "20241008", "20241101"))
